You can connect to Manticore Search through HTTP/HTTPS.
By default, Manticore listens for HTTP, HTTPS, and binary requests on ports 9308 and 9312.
In the "searchd" section of your configuration file, you can define the HTTP port using the listen directive as follows:
Both lines are valid and have the same meaning (except for the port number). They both define listeners that will serve all API/HTTP/HTTPS protocols. There are no special requirements, and any HTTP client can be used to connect to Manticore.
- HTTP
searchd {
...
listen = 127.0.0.1:9308
listen = 127.0.0.1:9312:http
...
}All HTTP endpoints return application/json content type. For the most part, endpoints use JSON payloads for requests. However, there are some exceptions that use NDJSON or simple URL-encoded payloads.
Currently, there is no user authentication. Therefore, make sure that the HTTP interface is not accessible to anyone outside your network. As Manticore functions like any other web server, you can use a reverse proxy, such as Nginx, to implement HTTP authentication or caching.
The HTTP protocol also supports SSL encryption:
If you specify :https instead of :http only secured connections will be accepted. Otherwise in case of no valid key/certificate provided, but the client trying to connect via https - the connection will be dropped. If you make not HTTPS, but an HTTP request to 9443 it will respond with HTTP code 400.
- HTTPS
searchd {
...
listen = 127.0.0.1:9308
listen = 127.0.0.1:9443:https
...
}Separate HTTP interface can be used for 'VIP' connections. In this case, the connection bypasses a thread pool and always creates a new dedicated thread. This is useful for managing Manticore Search during periods of severe overload when the server might stall or not allow regular port connections.
For more information on the listen directive, see this section.
- VIP
searchd {
...
listen = 127.0.0.1:9308
listen = 127.0.0.1:9318:_vip
...
}Manticore provides /sql, /cli, and /cli_json endpoints for running SQL queries over HTTP. Each endpoint is designed for specific use cases:
/sql: Suitable for programmatic usage from applications.- The
/sqlendpoint accepts only SELECT statements and returns the response in HTTP JSON format. - The
/sql?mode=rawendpoint accepts any SQL query and returns the response in raw format, similar to what you would receive via mysql.
- The
/cli: Intended only for manual use (e.g., via curl or browser). Not recommended for scripts./cli_json: Similar to/cli, but returns results in JSON format. Not recommended for scripts.
General syntax:
curl "localhost:6780/sql[?mode=raw]&query={URL_ENCODED_QUERY}"curl localhost:6780/sql[?mode=raw] -d "[query={URL_ENCODED_QUERY}|{NOT_URL_ENCODED_QUERY}]"
The /sql endpoint accepts an SQL query via the HTTP JSON interface:
- Without
mode=raw- only SELECTs are allowed, returning the response in JSON format. - With mode=raw - any SQL query is permitted, returning the response in raw format.
The endpoint can handle HTTP requests using either the GET or the POST method. For sending queries, you can:
- Using GET: Include the query in the
queryparameter of the URL, like/sql?query=your_encoded_query_here. It's important to URL encode this parameter to avoid errors, especially if the query includes an=sign, which might be interpreted as part of the URL syntax rather than the query. - Using POST: You can also send the query within the body of a POST request. When using this method:
- If you send the query as a parameter named
query, ensure it is URL encoded. - If you send the query directly as plain text (a raw POST body), do not URL encode it. This is useful when the query is long or complex, or if the query is stored in a file and you want to send it as is by pointing your HTTP client (e.g.,
curl) to it.
- If you send the query as a parameter named
This approach keeps the usage of GET and POST distinct and avoids any confusion about combining methods in a single request.
Without mode=raw the response is a JSON containing information about the hits and the execution time. The response format is the same as the json/search endpoint. Note that the /sql endpoint only supports single search requests. For processing a multi-query, see the section below about the raw mode.
- POST
- POST URL-encoded
- GET URL-encoded
POST /sql
select id,subject,author_id from forum where match('@subject php manticore') group by author_id order by id desc limit 0,5POST /sql query=select%20id%2Csubject%2Cauthor_id%20%20from%20forum%20where%20match%28%27%40subject%20php%20manticore%27%29%20group%20by%20author_id%20order%20by%20id%20desc%20limit%200%2C5GET /sql?query=select%20id%2Csubject%2Cauthor_id%20%20from%20forum%20where%20match%28%27%40subject%20php%20manticore%27%29%20group%20by%20author_id%20order%20by%20id%20desc%20limit%200%2C5{
"took": 0,
"timed_out": false,
"hits": {
"total": 2,
"total_relation": "eq",
"hits": [
{
"_id": 2,
"_score": 2356,
"_source": {
"subject": "php manticore",
"author_id": 12
}
},
{
"_id": 1,
"_score": 2356,
"_source": {
"subject": "php manticore",
"author_id": 11
}
}
]
}
}{
"took": 0,
"timed_out": false,
"hits": {
"total": 2,
"total_relation": "eq",
"hits": [
{
"_id": 2,
"_score": 2356,
"_source": {
"subject": "php manticore",
"author_id": 12
}
},
{
"_id": 1,
"_score": 2356,
"_source": {
"subject": "php manticore",
"author_id": 11
}
}
]
}
}{
"took": 0,
"timed_out": false,
"hits": {
"total": 2,
"total_relation": "eq",
"hits": [
{
"_id": 2,
"_score": 2356,
"_source": {
"subject": "php manticore",
"author_id": 12
}
},
{
"_id": 1,
"_score": 2356,
"_source": {
"subject": "php manticore",
"author_id": 11
}
}
]
}
}The /sql endpoint also includes a special "raw" mode, which allows you to send any valid SQL queries, including multi-queries. The response is a JSON array containing one or more result sets. You can activate this mode by using the option mode=raw.
- POST
- POST URL-encoded
- POST URL-encoded 2nd way
- GET URL-encoded
- curl examples
POST /sql?mode=raw
desc testPOST /sql?mode=raw
query=desc%20testPOST /sql
mode=raw&query=desc%20testGET /sql?mode=raw&query=desc%20test# POST:
curl localhost:9308/sql?mode=raw -d 'SHOW TABLES'
# POST, URL-encoded:
curl localhost:9308/sql?mode=raw -d 'query=SHOW%20TABLES'
# POST, URL-encoded, 2nd way:
curl localhost:9308/sql -d 'mode=raw&query=SHOW%20TABLES'
# POST, URL-non-encoded:
curl localhost:9308/sql -d 'mode=raw&query=SHOW TABLES'[
{
"columns": [
{
"Field": {
"type": "string"
}
},
{
"Type": {
"type": "string"
}
},
{
"Properties": {
"type": "string"
}
}
],
"data": [
{
"Field": "id",
"Type": "bigint",
"Properties": ""
},
{
"Field": "title",
"Type": "text",
"Properties": "indexed"
},
{
"Field": "gid",
"Type": "uint",
"Properties": ""
},
{
"Field": "title",
"Type": "string",
"Properties": ""
},
{
"Field": "j",
"Type": "json",
"Properties": ""
},
{
"Field": "new1",
"Type": "uint",
"Properties": ""
}
],
"total": 6,
"error": "",
"warning": ""
}
][
{
"columns": [
{
"Field": {
"type": "string"
}
},
{
"Type": {
"type": "string"
}
},
{
"Properties": {
"type": "string"
}
}
],
"data": [
{
"Field": "id",
"Type": "bigint",
"Properties": ""
},
{
"Field": "title",
"Type": "text",
"Properties": "indexed"
},
{
"Field": "gid",
"Type": "uint",
"Properties": ""
},
{
"Field": "title",
"Type": "string",
"Properties": ""
},
{
"Field": "j",
"Type": "json",
"Properties": ""
},
{
"Field": "new1",
"Type": "uint",
"Properties": ""
}
],
"total": 6,
"error": "",
"warning": ""
}
][
{
"columns": [
{
"Field": {
"type": "string"
}
},
{
"Type": {
"type": "string"
}
},
{
"Properties": {
"type": "string"
}
}
],
"data": [
{
"Field": "id",
"Type": "bigint",
"Properties": ""
},
{
"Field": "title",
"Type": "text",
"Properties": "indexed"
},
{
"Field": "gid",
"Type": "uint",
"Properties": ""
},
{
"Field": "title",
"Type": "string",
"Properties": ""
},
{
"Field": "j",
"Type": "json",
"Properties": ""
},
{
"Field": "new1",
"Type": "uint",
"Properties": ""
}
],
"total": 6,
"error": "",
"warning": ""
}
][
{
"columns": [
{
"Field": {
"type": "string"
}
},
{
"Type": {
"type": "string"
}
},
{
"Properties": {
"type": "string"
}
}
],
"data": [
{
"Field": "id",
"Type": "bigint",
"Properties": ""
},
{
"Field": "title",
"Type": "text",
"Properties": "indexed"
},
{
"Field": "gid",
"Type": "uint",
"Properties": ""
},
{
"Field": "title",
"Type": "string",
"Properties": ""
},
{
"Field": "j",
"Type": "json",
"Properties": ""
},
{
"Field": "new1",
"Type": "uint",
"Properties": ""
}
],
"total": 6,
"error": "",
"warning": ""
}
]NOTE:
/clirequires Manticore Buddy. If it doesn't work, make sure Buddy is installed.
NOTE: The
/cliendpoint is designed for manual interaction with Manticore using tools like curl or a browser. It is not intended for use in automated scripts. Use the/sqlendpoint instead.
While the /sql endpoint is useful for controlling Manticore programmatically from your application, there's also the /cli endpoint. This makes it easier to manually maintain a Manticore instance using curl or your browser. It accepts both POST and GET HTTP methods. Everything inputted after /cli? is understood by Manticore, even if it's not manually escaped with curl or automatically encoded by the browser. No query parameter is required. Importantly, the + sign is not changed to a space, eliminating the need for encoding it. For the POST method, Manticore accepts everything exactly as it is, without any changes. The response is in tabular format, similar to an SQL result set you might see in a MySQL client.
- POST
- GET
- Browser
- curl example
POST /cli
desc testGET /cli?desc%20test
curl 0:9308/cli -d 'desc test'+-------+--------+----------------+
| Field | Type | Properties |
+-------+--------+----------------+
| id | bigint | |
| body | text | indexed stored |
| title | string | |
+-------+--------+----------------+
3 rows in set (0.001 sec)+-------+--------+----------------+
| Field | Type | Properties |
+-------+--------+----------------+
| id | bigint | |
| body | text | indexed stored |
| title | string | |
+-------+--------+----------------+
3 rows in set (0.001 sec)+-------+--------+----------------+
| Field | Type | Properties |
+-------+--------+----------------+
| id | bigint | |
| title | text | indexed stored |
+-------+--------+----------------+
2 rows in set (0.001 sec)NOTE: The
/cli_jsonendpoint is designed for manual interaction with Manticore using tools like curl or a browser. It is not intended for use in automated scripts. Use the/sqlendpoint instead.
The /cli_json endpoint provides the same functionality as /cli, but the response format is JSON. It includes:
columnssection describing the schema.datasection with the actual data.- Summary section with "total", "error", and "warning".
- POST
- GET
- curl example
POST /cli_json
desc testGET /cli_json?desc%20testcurl 0:9308/cli_json -d 'desc test'[
{
"columns":[
{
"Field":{
"type":"string"
}
},
{
"Type":{
"type":"string"
}
},
{
"Properties":{
"type":"string"
}
}
],
"data":[
{
"Field":"id",
"Type":"bigint",
"Properties":""
},
{
"Field":"body",
"Type":"text",
"Properties":"indexed stored"
},
{
"Field":"title",
"Type":"string",
"Properties":""
}
],
"total":3,
"error":"",
"warning":""
}
][
{
"columns":[
{
"Field":{
"type":"string"
}
},
{
"Type":{
"type":"string"
}
},
{
"Properties":{
"type":"string"
}
}
],
"data":[
{
"Field":"id",
"Type":"bigint",
"Properties":""
},
{
"Field":"body",
"Type":"text",
"Properties":"indexed stored"
},
{
"Field":"title",
"Type":"string",
"Properties":""
}
],
"total":3,
"error":"",
"warning":""
}
][{
"columns":[{"Field":{"type":"string"}},{"Type":{"type":"string"}},{"Properties":{"type":"string"}}],
"data":[
{"Field":"id","Type":"bigint","Properties":""},
{"Field":"body","Type":"text","Properties":"indexed stored"},
{"Field":"title","Type":"string","Properties":""}
],
"total":3,
"error":"",
"warning":""
}]HTTP keep-alive is supported for the /sql, /sql?mode=raw, and /cli_json endpoints, but not for the /cli endpoint. This feature enables stateful interactions via the HTTP JSON interface, provided the client also supports keep-alive. For example, using the /cli_json endpoint, you can run a SHOW META command after a SELECT query, and it will behave similarly to interactions with Manticore through a MySQL client.
You can add, update, replace, and delete your indexed data using different ways provided by Manticore. Manticore supports working with external storages such as databases, XML, CSV, and TSV documents. For insert and delete operations, a transaction mechanism is supported.
Also, for insert and replace queries, Manticore supports Elasticsearch-like query format along with its own format. For details, see the corresponding examples in the Adding documents to a real-time table and REPLACE sections.
⪢ Adding documents to a table
If you're looking for information on adding documents to a plain table, please refer to the section on adding data from external storages.
Adding documents in real-time is supported only for Real-Time and percolate tables. The corresponding SQL command, HTTP endpoint, or client functions insert new rows (documents) into a table with the provided field values. It's not necessary for a table to exist before adding documents to it. If the table doesn't exist, Manticore will attempt to create it automatically. For more information, see Auto schema.
You can insert a single or multiple documents with values for all fields of the table or just a portion of them. In this case, the other fields will be filled with their default values (0 for scalar types, an empty string for text types).
Expressions are not currently supported in INSERT, so values must be explicitly specified.
The ID field/value can be omitted, as RT and PQ tables support auto-id functionality. You can also use 0 as the id value to force automatic ID generation. Rows with duplicate IDs will not be overwritten by INSERT. Instead, you can use REPLACE for that purpose.
When using the HTTP JSON protocol, you have two different request formats to choose from: a common Manticore format and an Elasticsearch-like format. Both formats are demonstrated in the examples below.
Additionally, when using the Manticore JSON request format, keep in mind that the doc node is required, and all the values should be provided within it.
- SQL
- JSON
- Elasticsearch
- PHP
- Python
- Javascript
- Java
- C#
General syntax:
INSERT INTO <table name> [(column, ...)]
VALUES (value, ...)
[, (...)]INSERT INTO products(title,price) VALUES ('Crossbody Bag with Tassel', 19.85);
INSERT INTO products(title) VALUES ('Crossbody Bag with Tassel');
INSERT INTO products VALUES (0,'Yellow bag', 4.95);POST /insert
{
"table":"products",
"id":1,
"doc":
{
"title" : "Crossbody Bag with Tassel",
"price" : 19.85
}
}
POST /insert
{
"table":"products",
"id":2,
"doc":
{
"title" : "Crossbody Bag with Tassel"
}
}
POST /insert
{
"table":"products",
"id":0,
"doc":
{
"title" : "Yellow bag"
}
}
NOTE:
_createrequires Manticore Buddy. If it doesn't work, make sure Buddy is installed.
POST /products/_create/3
{
"title": "Yellow Bag with Tassel",
"price": 19.85
}
POST /products/_create/
{
"title": "Red Bag with Tassel",
"price": 19.85
}
$index->addDocuments([
['id' => 1, 'title' => 'Crossbody Bag with Tassel', 'price' => 19.85]
]);
$index->addDocuments([
['id' => 2, 'title' => 'Crossbody Bag with Tassel']
]);
$index->addDocuments([
['id' => 0, 'title' => 'Yellow bag']
]);indexApi.insert({"table" : "test", "id" : 1, "doc" : {"title" : "Crossbody Bag with Tassel", "price" : 19.85}})
indexApi.insert({"table" : "test", "id" : 2, "doc" : {"title" : "Crossbody Bag with Tassel"}})
indexApi.insert({"table" : "test", "id" : 0, "doc" : {{"title" : "Yellow bag"}})res = await indexApi.insert({"table" : "test", "id" : 1, "doc" : {"title" : "Crossbody Bag with Tassel", "price" : 19.85}});
res = await indexApi.insert({"table" : "test", "id" : 2, "doc" : {"title" : "Crossbody Bag with Tassel"}});
res = await indexApi.insert({"table" : "test", "id" : 0, "doc" : {{"title" : "Yellow bag"}});InsertDocumentRequest newdoc = new InsertDocumentRequest();
HashMap<String,Object> doc = new HashMap<String,Object>(){{
put("title","Crossbody Bag with Tassel");
put("price",19.85);
}};
newdoc.index("products").id(1L).setDoc(doc);
sqlresult = indexApi.insert(newdoc);
newdoc = new InsertDocumentRequest();
HashMap<String,Object> doc = new HashMap<String,Object>(){{
put("title","Crossbody Bag with Tassel");
}};
newdoc.index("products").id(2L).setDoc(doc);
sqlresult = indexApi.insert(newdoc);
newdoc = new InsertDocumentRequest();
HashMap<String,Object> doc = new HashMap<String,Object>(){{
put("title","Yellow bag");
}};
newdoc.index("products").id(0L).setDoc(doc);
sqlresult = indexApi.insert(newdoc);
Dictionary<string, Object> doc = new Dictionary<string, Object>();
doc.Add("title", "Crossbody Bag with Tassel");
doc.Add("price", 19.85);
InsertDocumentRequest newdoc = new InsertDocumentRequest(index: "products", id: 1, doc: doc);
var sqlresult = indexApi.Insert(newdoc);
doc = new Dictionary<string, Object>();
doc.Add("title", "Crossbody Bag with Tassel");
newdoc = new InsertDocumentRequest(index: "products", id: 2, doc: doc);
sqlresult = indexApi.Insert(newdoc);
doc = new Dictionary<string, Object>();
doc.Add("title", "Yellow bag");
newdoc = new InsertDocumentRequest(index: "products", id: 0, doc: doc);
sqlresult = indexApi.Insert(newdoc);
Query OK, 1 rows affected (0.00 sec)
Query OK, 1 rows affected (0.00 sec)
Query OK, 1 rows affected (0.00 sec){
"table": "products",
"_id": 1,
"created": true,
"result": "created",
"status": 201
}
{
"table": "products",
"_id": 2,
"created": true,
"result": "created",
"status": 201
}
{
"table": "products",
"_id": 1657860156022587406,
"created": true,
"result": "created",
"status": 201
}
{
"_id":3,
"table":"products",
"_primary_term":1,
"_seq_no":0,
"_shards":{
"failed":0,
"successful":1,
"total":1
},
"_type":"_doc",
"_version":1,
"result":"updated"
}
{
"_id":2235747273424240642,
"table":"products",
"_primary_term":1,
"_seq_no":0,
"_shards":{
"failed":0,
"successful":1,
"total":1
},
"_type":"_doc",
"_version":1,
"result":"updated"
}NOTE: Auto schema requires Manticore Buddy. If it doesn't work, make sure Buddy is installed.
Manticore features an automatic table creation mechanism, which activates when a specified table in the insert query doesn't yet exist. This mechanism is enabled by default. To disable it, set auto_schema = 0 in the Searchd section of your Manticore config file.
By default, all text values in the VALUES clause are considered to be of the text type, except for values representing valid email addresses, which are treated as the string type.
If you attempt to INSERT multiple rows with different, incompatible value types for the same field, auto table creation will be canceled, and an error message will be returned. However, if the different value types are compatible, the resulting field type will be the one that accommodates all the values. Some automatic data type conversions that may occur include:
- mva -> mva64
- uint -> bigint -> float (this may cause some precision loss)
- string -> text
Also, the following formats of dates will be recognized and converted to timestamps while all other date formats will be treated as strings:
%Y-%m-%dT%H:%M:%E*S%Z%Y-%m-%d'T'%H:%M:%S%Z%Y-%m-%dT%H:%M:%E*S%Y-%m-%dT%H:%M:%s%Y-%m-%dT%H:%M%Y-%m-%dT%H
Keep in mind that the /bulk HTTP endpoint does not support automatic table creation (auto schema). Only the /_bulk (Elasticsearch-like) HTTP endpoint and the SQL interface support this feature.
- SQL
- JSON
MySQL [(none)]> drop table if exists t; insert into t(i,f,t,s,j,b,m,mb) values(123,1.2,'text here','test@mail.com','{"a": 123}',1099511627776,(1,2),(1099511627776,1099511627777)); desc t; select * from t;POST /insert -d
{
"table":"t",
"id": 2,
"doc":
{
"i" : 123,
"f" : 1.23,
"t": "text here",
"s": "test@mail.com",
"j": {"a": 123},
"b": 1099511627776,
"m": [1,2],
"mb": [1099511627776,1099511627777]
}
}--------------
drop table if exists t
--------------
Query OK, 0 rows affected (0.42 sec)
--------------
insert into t(i,f,t,j,b,m,mb) values(123,1.2,'text here','{"a": 123}',1099511627776,(1,2),(1099511627776,1099511627777))
--------------
Query OK, 1 row affected (0.00 sec)
--------------
desc t
--------------
+-------+--------+----------------+
| Field | Type | Properties |
+-------+--------+----------------+
| id | bigint | |
| t | text | indexed stored |
| s | string | |
| j | json | |
| i | uint | |
| b | bigint | |
| f | float | |
| m | mva | |
| mb | mva64 | |
+-------+--------+----------------+
8 rows in set (0.00 sec)
--------------
select * from t
--------------
+---------------------+------+---------------+----------+------+-----------------------------+-----------+---------------+------------+
| id | i | b | f | m | mb | t | s | j |
+---------------------+------+---------------+----------+------+-----------------------------+-----------+---------------+------------+
| 5045949922868723723 | 123 | 1099511627776 | 1.200000 | 1,2 | 1099511627776,1099511627777 | text here | test@mail.com | {"a": 123} |
+---------------------+------+---------------+----------+------+-----------------------------+-----------+---------------+------------+
1 row in set (0.00 sec){"table":"t","_id":2,"created":true,"result":"created","status":201}Manticore provides an auto ID generation functionality for the column ID of documents inserted or replaced into a real-time or Percolate table. The generator produces a unique ID for a document with some guarantees, but it should not be considered an auto-incremented ID.
The generated ID value is guaranteed to be unique under the following conditions:
- The server_id value of the current server is in the range of 0 to 127 and is unique among nodes in the cluster, or it uses the default value generated from the MAC address as a seed
- The system time does not change for the Manticore node between server restarts
- The auto ID is generated fewer than 16 million times per second between search server restarts
The auto ID generator creates a 64-bit integer for a document ID and uses the following schema:
- Bits 0 to 23 form a counter that gets incremented on every call to the auto ID generator
- Bits 24 to 55 represent the Unix timestamp of the server start
- Bits 56 to 63 correspond to the server_id
This schema ensures that the generated ID is unique among all nodes in the cluster and that data inserted into different cluster nodes does not create collisions between the nodes.
As a result, the first ID from the generator used for auto ID is NOT 1 but a larger number. Additionally, the document stream inserted into a table might have non-sequential ID values if inserts into other tables occur between calls, as the ID generator is singular in the server and shared between all its tables.
- SQL
- JSON
- PHP
- Python
- Javascript
- Java
- C#
INSERT INTO products(title,price) VALUES ('Crossbody Bag with Tassel', 19.85);
INSERT INTO products VALUES (0,'Yello bag', 4.95);
select * from products;POST /insert
{
"table":"products",
"id":0,
"doc":
{
"title" : "Yellow bag"
}
}
GET /search
{
"table":"products",
"query":{
"query_string":""
}
}$index->addDocuments([
['id' => 0, 'title' => 'Yellow bag']
]);indexApi.insert({"table" : "products", "id" : 0, "doc" : {"title" : "Yellow bag"}})res = await indexApi.insert({"table" : "products", "id" : 0, "doc" : {"title" : "Yellow bag"}});newdoc = new InsertDocumentRequest();
HashMap<String,Object> doc = new HashMap<String,Object>(){{
put("title","Yellow bag");
}};
newdoc.index("products").id(0L).setDoc(doc);
sqlresult = indexApi.insert(newdoc);Dictionary<string, Object> doc = new Dictionary<string, Object>();
doc.Add("title", "Yellow bag");
InsertDocumentRequest newdoc = new InsertDocumentRequest(index: "products", id: 0, doc: doc);
var sqlresult = indexApi.Insert(newdoc);+---------------------+-----------+---------------------------+
| id | price | title |
+---------------------+-----------+---------------------------+
| 1657860156022587404 | 19.850000 | Crossbody Bag with Tassel |
| 1657860156022587405 | 4.950000 | Yello bag |
+---------------------+-----------+---------------------------+{
"took": 0,
"timed_out": false,
"hits": {
"total": 1,
"hits": [
{
"_id": 1657860156022587406,
"_score": 1,
"_source": {
"price": 0,
"title": "Yellow bag"
}
}
]
}
}CALL UUID_SHORT(N)The CALL UUID_SHORT(N) statement allows for generating N unique 64-bit IDs in a single call without inserting any documents. It is particularly useful when you need to pre-generate IDs in Manticore for use in other systems or storage solutions. For example, you can generate auto-IDs in Manticore and then use them in another database, application, or workflow, ensuring consistent and unique identifiers across different environments.
- Example
CALL UUID_SHORT(3)+---------------------+
| uuid_short() |
+---------------------+
| 1227930988733973183 |
| 1227930988733973184 |
| 1227930988733973185 |
+---------------------+You can insert not just a single document into a real-time table, but as many as you'd like. It's perfectly fine to insert batches of tens of thousands of documents into a real-time table. However, it's important to keep the following points in mind:
- The larger the batch, the higher the latency of each insert operation
- The larger the batch, the higher the indexation speed you can expect
- You might want to increase the max_packet_size value to allow for larger batches
- Normally, each batch insert operation is considered a single transaction with atomicity guarantee, so you will either have all the new documents in the table at once or, in case of failure, none of them will be added. See more details about an empty line or switching to another table in the "JSON" example.
Note that the /bulk HTTP endpoint does not support automatic creation of tables (auto schema). Only the /_bulk (Elasticsearch-like) HTTP endpoint and the SQL interface support this feature. The /_bulk (Elasticsearch-like) HTTP endpoint allows the table name to include the cluster name in the format cluster_name:table_name.
/_bulk endpoint accepts document IDs in the same format as Elasticsearch, and you can also include the id within the document itself:
{ "index": { "table" : "products", "_id" : "1" } }
{ "title" : "Crossbody Bag with Tassel", "price": 19.85 }or
{ "index": { "table" : "products" } }
{ "title" : "Crossbody Bag with Tassel", "price": 19.85, "id": "1" }The /bulk (Manticore mode) endpoint supports Chunked transfer encoding. You can use it to transmit large batches. It:
- reduces peak RAM usage, lowering the risk of OOM
- decreases response time
- allows you to bypass max_packet_size and transfer batches much larger than the maximum allowed value of
max_packet_size(128MB), for example, 1GB at a time.
- SQL
- JSON
- Elasticsearch
- PHP
- Python
- Javascript
- Java
- C#
For bulk insert, simply provide more documents in brackets after VALUES(). The syntax is:
INSERT INTO <table name>[(column1, column2, ...)] VALUES(value1[, value2 , ...]), (...)The optional column name list allows you to explicitly specify values for some of the columns present in the table. All other columns will be filled with their default values (0 for scalar types, empty string for string types).
For example:
INSERT INTO products(title,price) VALUES ('Crossbody Bag with Tassel', 19.85), ('microfiber sheet set', 19.99), ('Pet Hair Remover Glove', 7.99);The syntax is generally the same as for inserting a single document. Just provide more lines, one for each document, and use the /bulk endpoint instead of /insert. Enclose each document in the "insert" node. Note that it also requires:
Content-Type: application/x-ndjson- The data should be formatted as newline-delimited JSON (NDJSON). Essentially, this means that each line should contain exactly one JSON statement and end with a newline
\nand possibly\r.
The /bulk endpoint supports 'insert', 'replace', 'delete', and 'update' queries. Keep in mind that you can direct operations to multiple tables, but transactions are only possible for a single table. If you specify more, Manticore will gather operations directed to one table into a single transaction. When the table changes, it will commit the collected operations and initiate a new transaction on the new table. An empty line separating batches also leads to committing the previous batch and starting a new transaction.
In the response for a /bulk request, you can find the following fields:
- "errors": shows whether any errors occurred (true/false)
- "error": describes the error that took place
- "current_line": the line number where execution stopped (or failed); empty lines, including the first empty line, are also counted
- "skipped_lines": the count of non-committed lines, beginning from the
current_lineand moving backward
POST /bulk
-H "Content-Type: application/x-ndjson" -d '
{"insert": {"table":"products", "id":1, "doc": {"title":"Crossbody Bag with Tassel","price" : 19.85}}}
{"insert":{"table":"products", "id":2, "doc": {"title":"microfiber sheet set","price" : 19.99}}}
'
POST /bulk
-H "Content-Type: application/x-ndjson" -d '
{"insert":{"table":"test1","id":21,"doc":{"int_col":1,"price":1.1,"title":"bulk doc one"}}}
{"insert":{"table":"test1","id":22,"doc":{"int_col":2,"price":2.2,"title":"bulk doc two"}}}
{"insert":{"table":"test1","id":23,"doc":{"int_col":3,"price":3.3,"title":"bulk doc three"}}}
{"insert":{"table":"test2","id":24,"doc":{"int_col":4,"price":4.4,"title":"bulk doc four"}}}
{"insert":{"table":"test2","id":25,"doc":{"int_col":5,"price":5.5,"title":"bulk doc five"}}}
'NOTE:
_bulkrequires Manticore Buddy if the table doesn't exist yet. If it doesn't work, make sure Buddy is installed.
POST /_bulk
-H "Content-Type: application/x-ndjson" -d '
{ "index" : { "table" : "products" } }
{ "title" : "Yellow Bag", "price": 12 }
{ "create" : { "table" : "products" } }
{ "title" : "Red Bag", "price": 12.5, "id": 3 }
'Use method addDocuments():
$index->addDocuments([
['id' => 1, 'title' => 'Crossbody Bag with Tassel', 'price' => 19.85],
['id' => 2, 'title' => 'microfiber sheet set', 'price' => 19.99],
['id' => 3, 'title' => 'Pet Hair Remover Glove', 'price' => 7.99]
]);docs = [ \
{"insert": {"table" : "products", "id" : 1, "doc" : {"title" : "Crossbody Bag with Tassel", "price" : 19.85}}}, \
{"insert": {"table" : "products", "id" : 2, "doc" : {"title" : "microfiber sheet set", "price" : 19.99}}}, \
{"insert": {"table" : "products", "id" : 3, "doc" : {"title" : "CPet Hair Remover Glove", "price" : 7.99}}}
]
res = indexApi.bulk('\n'.join(map(json.dumps,docs)))let docs = [
{"insert": {"table" : "products", "id" : 3, "doc" : {"title" : "Crossbody Bag with Tassel", "price" : 19.85}}},
{"insert": {"table" : "products", "id" : 4, "doc" : {"title" : "microfiber sheet set", "price" : 19.99}}},
{"insert": {"table" : "products", "id" : 5, "doc" : {"title" : "CPet Hair Remover Glove", "price" : 7.99}}}
];
res = await indexApi.bulk(docs.map(e=>JSON.stringify(e)).join('\n'));String body = "{\"insert\": {\"index\" : \"products\", \"id\" : 1, \"doc\" : {\"title\" : \"Crossbody Bag with Tassel\", \"price\" : 19.85}}}"+"\n"+
"{\"insert\": {\"index\" : \"products\", \"id\" : 4, \"doc\" : {\"title\" : \"microfiber sheet set\", \"price\" : 19.99}}}"+"\n"+
"{\"insert\": {\"index\" : \"products\", \"id\" : 5, \"doc\" : {\"title\" : \"CPet Hair Remover Glove\", \"price\" : 7.99}}}"+"\n";
BulkResponse bulkresult = indexApi.bulk(body);string body = "{\"insert\": {\"index\" : \"products\", \"id\" : 1, \"doc\" : {\"title\" : \"Crossbody Bag with Tassel\", \"price\" : 19.85}}}"+"\n"+
"{\"insert\": {\"index\" : \"products\", \"id\" : 4, \"doc\" : {\"title\" : \"microfiber sheet set\", \"price\" : 19.99}}}"+"\n"+
"{\"insert\": {\"index\" : \"products\", \"id\" : 5, \"doc\" : {\"title\" : \"CPet Hair Remover Glove\", \"price\" : 7.99}}}"+"\n";
BulkResponse bulkresult = indexApi.Bulk(string.Join("\n", docs));Query OK, 3 rows affected (0.01 sec)Expressions are currently not supported in INSERT, and values should be explicitly specified.
{
"items": [
{
"bulk": {
"table": "products",
"_id": 2,
"created": 2,
"deleted": 0,
"updated": 0,
"result": "created",
"status": 201
}
}
],
"current_line": 4,
"skipped_lines": 0,
"errors": false,
"error": ""
}
{
"items": [
{
"bulk": {
"table": "test1",
"_id": 22,
"created": 2,
"deleted": 0,
"updated": 0,
"result": "created",
"status": 201
}
},
{
"bulk": {
"table": "test1",
"_id": 23,
"created": 1,
"deleted": 0,
"updated": 0,
"result": "created",
"status": 201
}
},
{
"bulk": {
"table": "test2",
"_id": 25,
"created": 2,
"deleted": 0,
"updated": 0,
"result": "created",
"status": 201
}
}
],
"current_line": 8,
"skipped_lines": 0,
"errors": false,
"error": ""
}{
"items": [
{
"table": {
"table": "products",
"_type": "doc",
"_id": 1657860156022587406,
"_version": 1,
"result": "created",
"_shards": {
"total": 1,
"successful": 1,
"failed": 0
},
"_seq_no": 0,
"_primary_term": 1,
"status": 201
}
},
{
"create": {
"table": "products",
"_type": "doc",
"_id": 3,
"_version": 1,
"result": "created",
"_shards": {
"total": 1,
"successful": 1,
"failed": 0
},
"_seq_no": 0,
"_primary_term": 1,
"status": 201
}
}
],
"errors": false,
"took": 1
}- SQL
- JSON
- Elasticsearch
- PHP
- Python
- Javascript
- Java
- C#
INSERT INTO products(title, sizes) VALUES('shoes', (40,41,42,43));
POST /insert
{
"table":"products",
"id":1,
"doc":
{
"title" : "shoes",
"sizes" : [40, 41, 42, 43]
}
}POST /products/_create/1
{
"title": "shoes",
"sizes" : [40, 41, 42, 43]
}Or, alternatively
POST /products/_doc/
{
"title": "shoes",
"sizes" : [40, 41, 42, 43]
}$index->addDocument(
['title' => 'shoes', 'sizes' => [40,41,42,43]],
1
);indexApi.insert({"table" : "products", "id" : 0, "doc" : {"title" : "Yellow bag","sizes":[40,41,42,43]}})res = await indexApi.insert({"table" : "products", "id" : 0, "doc" : {"title" : "Yellow bag","sizes":[40,41,42,43]}});newdoc = new InsertDocumentRequest();
HashMap<String,Object> doc = new HashMap<String,Object>(){{
put("title","Yellow bag");
put("sizes",new int[]{40,41,42,43});
}};
newdoc.index("products").id(0L).setDoc(doc);
sqlresult = indexApi.insert(newdoc);Dictionary<string, Object> doc = new Dictionary<string, Object>();
doc.Add("title", "Yellow bag");
doc.Add("sizes", new List<Object> {40,41,42,43});
InsertDocumentRequest newdoc = new InsertDocumentRequest(index: "products", id: 0, doc: doc);
var sqlresult = indexApi.Insert(newdoc);JSON value can be inserted as an escaped string (via SQL or JSON) or as a JSON object (via the JSON interface).
- SQL
- JSON
- Elasticsearch
- PHP
- Python
- Javascript
- Java
- C#
INSERT INTO products VALUES (1, 'shoes', '{"size": 41, "color": "red"}');JSON value can be inserted as a JSON object
POST /insert
{
"table":"products",
"id":1,
"doc":
{
"title" : "shoes",
"meta" : {
"size": 41,
"color": "red"
}
}
}JSON value can be also inserted as a string containing escaped JSON:
POST /insert
{
"table":"products",
"id":1,
"doc":
{
"title" : "shoes",
"meta" : "{\"size\": 41, \"color\": \"red\"}"
}
}POST /products/_create/1
{
"title": "shoes",
"meta" : {
"size": 41,
"color": "red"
}
}Or, alternatively
POST /products/_doc/
{
"title": "shoes",
"meta" : {
"size": 41,
"color": "red"
}
}$index->addDocument(
['title' => 'shoes', 'meta' => '{"size": 41, "color": "red"}'],
1
);indexApi = api = manticoresearch.IndexApi(client)
indexApi.insert({"table" : "products", "id" : 0, "doc" : {"title" : "Yellow bag","meta":'{"size": 41, "color": "red"}'}})
res = await indexApi.insert({"table" : "products", "id" : 0, "doc" : {"title" : "Yellow bag","meta":'{"size": 41, "color": "red"}'}});newdoc = new InsertDocumentRequest();
HashMap<String,Object> doc = new HashMap<String,Object>(){{
put("title","Yellow bag");
put("meta",
new HashMap<String,Object>(){{
put("size",41);
put("color","red");
}});
}};
newdoc.index("products").id(0L).setDoc(doc);
sqlresult = indexApi.insert(newdoc);Dictionary<string, Object> meta = new Dictionary<string, Object>();
meta.Add("size", 41);
meta.Add("color", "red");
Dictionary<string, Object> doc = new Dictionary<string, Object>();
doc.Add("title", "Yellow bag");
doc.Add("meta", meta);
InsertDocumentRequest newdoc = new InsertDocumentRequest(index: "products", id: 0, doc: doc);
var sqlresult = indexApi.Insert(newdoc);